package com.ttmusic.dao;

import java.util.List;

import com.ttmusic.entity.AbstractMusic;
import com.ttmusic.entity.MusicInfo;
import com.ttmusic.utils.MusicUtils;
import com.ttmusic.utils.SQLite.SQLiteUtil;

import android.database.Cursor;

/**
 * 音乐信息dao
 * @author Administrator
 *
 */
public class MusicInfoDao implements BaseDao<AbstractMusic>{
	
	//单例模式
	private MusicInfoDao() {}
	private static MusicInfoDao musicDao = new MusicInfoDao();
	public static MusicInfoDao getInstance() {
		return musicDao;
	}

	/**
	 * 查找所有歌曲信息
	 */
	@Override
	public List<AbstractMusic> findAll() {
		String sql = "select SONG_ID,ALBUM_ID,ARTIST_ID,TITLE,ALBUM,ARTIST,DURATION,PATH,ADD_TIME,FAVORITE from TB_MUSIC_INFO ORDER BY SONG_ID ASC";
		Cursor cursor = SQLiteUtil.executeQuery(sql);
		return MusicUtils.getMusicList(cursor);
	}

	/**
	 * 通过主键id查找一首歌曲信息
	 */
	@Override
	public AbstractMusic loadById(Long id) {
		String sql = "select SONG_ID,ALBUM_ID,ARTIST_ID,TITLE,ALBUM,ARTIST,DURATION,PATH,ADD_TIME,FAVORITE from TB_MUSIC_INFO where SONG_ID=?";
		Cursor cursor = SQLiteUtil.executeQuery(sql, id.toString());
		List<AbstractMusic> list = MusicUtils.getMusicList(cursor);
		if(null != list && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}
	
	/**
	 * 通过主键id查找一首歌曲信息
	 */
	@Override
	public AbstractMusic loadByName(String title) {
		String sql = "select SONG_ID,ALBUM_ID,ARTIST_ID,TITLE,ALBUM,ARTIST,DURATION,PATH,ADD_TIME,FAVORITE from TB_MUSIC_INFO where TITLE=?";
		Cursor cursor = SQLiteUtil.executeQuery(sql, title);
		List<AbstractMusic> list = MusicUtils.getMusicList(cursor);
		if(null != list && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}
	
	/**
	 * 通过歌手id查询该歌手的所有歌曲
	 * @param artistId
	 * @return
	 */
	public List<AbstractMusic> loadByArtistId(Long artistId){
		String sql = "select SONG_ID,ALBUM_ID,ARTIST_ID,TITLE,ALBUM,ARTIST,DURATION,PATH,ADD_TIME,FAVORITE from TB_MUSIC_INFO where ARTIST_ID=?";
		Cursor cursor = SQLiteUtil.executeQuery(sql, artistId+"");
		return MusicUtils.getMusicList(cursor);
	}

	/**
	 * 保存歌曲信息到自己创建的TB_MUSIC_INFO表中
	 * @param artist
	 */
	@Override
	public long save(AbstractMusic music) {
		MusicInfo mi = (MusicInfo)music;
		//插入数据时不填主键  自动递增
		String sql = "insert into TB_MUSIC_INFO(ALBUM_ID,ARTIST_ID,TITLE,ALBUM,ARTIST,DURATION,PATH,ADD_TIME,FAVORITE) values(?,?,?,?,?,?,?,?,?)";
		SQLiteUtil.executeDML(sql, mi.getAlbumId(), mi.getArtistId(), mi.getTitle(), mi.getAlbum(), mi.getArtist(), mi.getDuration(), mi.getPath(), mi.getAddTime(), mi.getFavorite());
	
		//查找当前插入的数据主键值
		sql = "select MAX(SONG_ID) as MAX_SONG_ID from TB_MUSIC_INFO";
		Cursor c = SQLiteUtil.executeQuery(sql);
		if(c.getCount() > 0 && c.getColumnCount() > 0) {
			c.moveToNext();
			return c.getLong(c.getColumnIndex("MAX_SONG_ID"));
		}
		return -1;
	}

	/**
	 * 修改歌曲信息
	 */
	@Override
	public void update(AbstractMusic music) {
		MusicInfo mi = (MusicInfo)music;
		String sql = "update TB_MUSIC_INFO set TITLE=?,ALBUM=?,ARTIST=?,FAVORITE=? where SONG_ID=?";
		SQLiteUtil.executeDML(sql, mi.getTitle(), mi.getAlbum(), mi.getArtist(), mi.getFavorite(), mi.getSongId());
	}

	/**
	 * 删除歌曲信息
	 */
	@Override
	public void delete(Long id) {
		String sql = "delete from TB_MUSIC_INFO where SONG_ID=?";
		SQLiteUtil.executeDML(sql, id);
	}
	
	//-------------------------------以下是歌单与歌曲关联数据处理-----------------------------
	/**
	 * 通过歌单id查询该歌单的歌曲
	 * @param listId
	 * @return
	 */
	public List<AbstractMusic> findAllByListId(Integer listId){
		String sql = "select M.SONG_ID,M.ALBUM_ID,M.ARTIST_ID,M.TITLE,M.ALBUM,M.ARTIST,M.DURATION,M.PATH,M.ADD_TIME,M.FAVORITE from TB_MUSIC_INFO M,TB_MUSICLIST_MUSICINFO LM where M.SONG_ID=LM.SONG_ID and LM.LIST_ID=?";
		Cursor c = SQLiteUtil.executeQuery(sql, listId.toString());
		return MusicUtils.getMusicList(c);
	}
}
